﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp
{
    public partial class Form16 : Form
    {
       SqlConnection sqlConnection = null;
        SqlDataAdapter SqlDataAdapter = null;
        // 数据集是存储数据的对象,既可以存储数据也可以存储非数据库的数据,例如xml数据
        // 数据集独立于数据库,不直接与数据库交互
        // 数据集包含0个或多个表对象, 这些表对象由数据行和列,约束和有关表中数据关系的信息组成

        DataSet DataSet = null;
        public Form16()
        {
            InitializeComponent();
        }


        public string conStr = "Data Source=43.139.213.82;Initial Catalog=school;Persist Security Info=True;User ID=sa;Password=fgb@1357426";
        /// <summary>
        ///  表格事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox1.Text = dataGridView1.CurrentRow.Cells["Num"].Value.ToString();
            textBox2.Text = dataGridView1.CurrentRow.Cells["Name"].Value.ToString();
            textBox3.Text = dataGridView1.CurrentRow.Cells["Score"].Value.ToString();
        }

        private void textBox3_TextChanged(object sender, EventArgs e)
        {

        }

        private void Form16_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            //连接数据库 
            sqlConnection=new SqlConnection(this.conStr);
            // 查询数据执行sql指令
            SqlDataAdapter = new SqlDataAdapter("select * from student", sqlConnection);
            DataSet= new DataSet();
            // 填充数据
            SqlDataAdapter.Fill(DataSet, "student");
            // 数据加载表格
            dataGridView1.DataSource = DataSet.Tables["student"];
        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            // 创建sql命令
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(SqlDataAdapter);
            // 表格新建一行数据
            DataRow dataRow = DataSet.Tables["student"].NewRow();
            // 将值传给datarow数据
            dataRow[0] = textBox1.Text;
            dataRow[1] = textBox2.Text;
            dataRow[2] = textBox3.Text;
            // 数据集添加这行数据
            DataSet.Tables[0].Rows.Add(dataRow);
            //数据写入表
            SqlDataAdapter.Update(DataSet, "student");
            // 数据回显表格
            dataGridView1.DataSource = DataSet.Tables["student"];
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(SqlDataAdapter);
            // 获取表中所有行的数据
            DataRowCollection dataRowCollection = DataSet.Tables["student"].Rows;
            // 获取某行数据
            DataRow dataRow;
            for (int i = 0; i < dataRowCollection.Count; i++)
            {
                // 遍历每行数据给datarow
                dataRow = dataRowCollection[i];
                // datarow判断填写数据是否与当前行匹配
                //如果匹配执行更新操作
                if (dataRow["Num"].ToString() == textBox1.Text)
                {
                    // 更新成绩
                    dataRow["Score"] = textBox3.Text;
                }
            }
            // 数据 回显
            dataGridView1.DataSource = DataSet.Tables["student"];
            // 数据更新表
            SqlDataAdapter.Update(DataSet, "student");
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            SqlCommandBuilder sqlCommandBuilder=new SqlCommandBuilder(SqlDataAdapter);
            DataRowCollection dataRowCollection = DataSet.Tables["student"].Rows;
            DataRow dataRow;
            for (int i = 0; i < dataRowCollection.Count; i++) { 
                dataRow= dataRowCollection[i];
                if (dataRow["Num"] .ToString()== textBox1.Text) {
                    // 删除操作
                    dataRow.Delete();
                }
            }
            // 数据更新
            SqlDataAdapter.Update(DataSet, "student");
            // 数据回显表格
            dataGridView1.DataSource = DataSet.Tables["student"];
        }
    }
}
